package com.sf.tools.generate;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import com.sf.tools.common.CheckI18NProgressHandler;
import com.sf.tools.common.ConnectionFactory;

public class DataCreator {

	private CheckI18NProgressHandler progressHandler;
	
	private final String ALL_TABLES = "select a.table_name,b.comments from user_tables a,user_tab_comments b where a.table_name = b.table_name";
	
	private final String TABLE_INFOS = "select a.column_name, a.data_type, "
			+ " decode(a.data_type,'NUMBER',a.data_precision,a.data_length) as data_length, a.data_scale as data_scale, "
			+ " decode(e.key,'Y','Y','N') as is_primary, "
			+ " f.comments as comments, a.nullable as nullable, a.data_default as data_default "
			+ " from user_tab_columns a, user_col_comments f, "
			+ " (select b.table_name, b.index_name,b.uniqueness, c.column_name, "
			+ " decode(d.constraint_name,NULL,'N','Y') key "
			+ " from user_indexes b, user_ind_columns c, "
			+ " ( select constraint_name from user_constraints where constraint_type='P' ) d "
			+ " where b.index_name=c.index_name and b.index_name=d.constraint_name(+) ) e "
			+ " where a.table_name=? and a.table_name=e.table_name(+) and a.column_name=e.column_name(+) "
			+ " and a.table_name=f.table_name and a.column_name=f.column_name "
			+ " order by a.column_id ";
	
	private Connection conn = null;
	private PreparedStatement pstm=null;
	private ResultSet rs = null;
	/**
	 * 获取所有表名
	 * @return
	 */
	public List<String[]> findAllTables(){
		List<String[]> list = new ArrayList<String[]>();
		try {
			conn = ConnectionFactory.getIntance().getConnection();
			pstm = conn.prepareStatement(ALL_TABLES);
			rs = pstm.executeQuery();
			while(rs.next()){
				String[] row = new String[2];
				for(int i=1;i<=2;i++){
//					System.out.print(rs.getString(i) + "  ");
					row[i-1] = rs.getString(i);
				}
//				System.out.println();
				list.add(row);
//				System.out.println(row[0] + " - " + row[1]);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return list;
	}
	
	/**
	 * 获取表信息
	 * @param tableName
	 * @return
	 */
	public List<String[]> findTableInfos(String tableName){
		List<String[]> list = new ArrayList<String[]>();
		try {
			System.out.println(TABLE_INFOS);
			conn = ConnectionFactory.getIntance().getConnection();
			pstm = conn.prepareStatement(TABLE_INFOS);
			pstm.setString(1, tableName);
			rs = pstm.executeQuery();
			while(rs.next()){
				String[] row = new String[8];
				String columnName = rs.getString(1);
				String dataType = rs.getString(2);
				String dataLength = rs.getString(3);
				String dataScale = rs.getString(4);
				String isPrimary = rs.getString(5);
				String comments = rs.getString(6);
				String nullable = rs.getString(7);
				Object dataDefault = rs.getObject(8);
				System.out.println(columnName+"="+dataType+"="+dataLength+"="+dataScale+"="+isPrimary+"="+comments+"="+nullable+"="+dataDefault);
				row[0] = columnName;
				row[1] = dataType;
				row[2] = dataLength;
				row[3] = dataScale;
				row[4] = isPrimary;
				row[5] = comments;
				row[6] = nullable;
				row[7] = dataDefault!=null?dataDefault.toString():null;
				list.add(row);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return list;
	}
	
	public void start(String url, String user, String pass, String driver, String path) {
		WritableWorkbook book = null;
		WritableSheet sheet = null;
		FileOutputStream fos = null;
		try {
			ConnectionFactory.getIntance().init(driver, url, user, pass);
			List<String[]> tables = findAllTables();
			if(tables.isEmpty()){
				return ;
			}
			this.progressHandler.loadTaskCount(tables.size(),null);
			
			fos = new FileOutputStream(new File(path));
			book = Workbook.createWorkbook(fos);
			sheet = book.createSheet("数据字典", 0);
			
			int rowIndex = 1;
			for (String[] tableInfo : tables) {
				String tableName = tableInfo[0];
				String comments = tableInfo[1];
				System.out.println(tableName +" - "+ comments);
				List<String[]> columnInfos = findTableInfos(tableName);
				
				sheet.mergeCells(3, rowIndex, 4, rowIndex);
				sheet.setColumnView(2, 20);
				sheet.setColumnView(3, 18);
				sheet.setColumnView(4, 10);
				sheet.setColumnView(5, 10);
				sheet.setColumnView(6, 15);
				sheet.setColumnView(7, 20);
				WritableCellFormat wc = new WritableCellFormat();
				// 设置居中   
//				wc.setAlignment(Alignment.CENTRE);   
				WritableFont wf = new WritableFont(WritableFont.createFont("宋体"), 11);
				wc.setFont(wf);
				// 设置边框线   
				wc.setBorder(Border.ALL, BorderLineStyle.THIN);   
				// 设置单元格的背景颜色   
				wc.setBackground(jxl.format.Colour.GREY_25_PERCENT);
//				String header = "";
//				if(comments!=null){
//					header = comments+" "+tableName;
//				}else{
//					header = tableName;
//				}
				sheet.addCell(new Label(2, rowIndex, comments,wc));
				sheet.addCell(new Label(3, rowIndex, tableName,wc));
				rowIndex++;
				
				wc = new WritableCellFormat();
				wc.setBorder(Border.ALL, BorderLineStyle.THIN);
				wc.setAlignment(Alignment.CENTRE);
				wc.setBackground(jxl.format.Colour.AQUA);
				sheet.addCell(new Label(2, rowIndex, "字段",wc));
				sheet.addCell(new Label(3, rowIndex, "类型",wc));
				sheet.addCell(new Label(4, rowIndex, "是否为空",wc));
				sheet.addCell(new Label(5, rowIndex, "主键",wc));
				sheet.addCell(new Label(6, rowIndex, "默认值",wc));
				sheet.addCell(new Label(7, rowIndex, "描述",wc));
				rowIndex++;
				
				wc = new WritableCellFormat();
				wc.setBorder(Border.ALL, BorderLineStyle.THIN);
				wf = new WritableFont(WritableFont.createFont("宋体"), 10);
				wc.setFont(wf);
				
				for (String[] columnInfo : columnInfos) {
					//字段
					sheet.addCell(new Label(2,rowIndex,columnInfo[0],wc));
					//类型
					String dataType = columnInfo[1];
					String precision =  columnInfo[2];
					String scale =  columnInfo[3];
					if("NUMBER".equals(dataType)){
						if(precision!=null && scale!=null){
							dataType=dataType+"("+precision+","+scale+")";
						}else if(precision!=null && scale==null){
							dataType=dataType+"("+precision+")";
						}
					}
					if("CHAR".equals(dataType) || "VARCHAR2".equals(dataType) || "NVARCHAR2".equals(dataType)){
						dataType=dataType+"("+precision+")";
					}
					sheet.addCell(new Label(3,rowIndex,dataType,wc));
					//是否为空
					sheet.addCell(new Label(4,rowIndex,columnInfo[6],wc));
					//主键
					sheet.addCell(new Label(5,rowIndex,columnInfo[4],wc));
					//默认值
					sheet.addCell(new Label(6,rowIndex,columnInfo[7],wc));
					//描述
					sheet.addCell(new Label(7,rowIndex,columnInfo[5],wc));
					rowIndex++;
				}
				rowIndex+=2;
				this.progressHandler.doneTaskCount();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			ConnectionFactory.getIntance().close(rs, pstm, conn);
			try {
				book.write();
			} catch (IOException e) {
				e.printStackTrace();
			}
			try {
				book.close();
				fos.close();
			} catch (Exception e) {
				e.printStackTrace();
			} finally{
				fos = null;
				book = null;
			}
		}
	}

	

	public CheckI18NProgressHandler getProgressHandler() {
		return progressHandler;
	}

	public void setProgressHandler(CheckI18NProgressHandler progressHandler) {
		this.progressHandler = progressHandler;
	}

}
